package edu.uoc.tdp.pac3.server;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.sql.Statement;
import javax.swing.JOptionPane;

import edu.uoc.tdp.pac3.beans.Incidencia;
import edu.uoc.tdp.pac3.common.tools.TDSLanguageUtils;

/**
 * Classe per gestionar l'acces a la Taula d'incidencies
 * 
 * @author Eloi Rebollo i Francesc Cabrera
 * @version 1.0
 */
public class GestorIncidencies {

    private Connection objConn;
    private TDSLanguageUtils mess = null;

    public GestorIncidencies(GestorDisc gestor) {
        objConn = gestor.getConnection();
    }

    public Connection getConnection() {
        return objConn;
    }

    /**
     * Recerca un incidencia segons l'identificador
     * @param Identificador de la incidencia
     * @return L'objecte de la classe incidencia amb identificador igual que el parametre
     */
    public Incidencia getRequest(int idRequest) {
        Incidencia request = null;

        String consulta = "SELECT * FROM tb_request WHERE request_id =  " + idRequest;
        try {
            Statement query = getConnection().createStatement();
            ResultSet rs = query.executeQuery(consulta);
            if (rs.next()) {
                request = new Incidencia(rs.getInt(1), rs.getInt(2), rs.getInt(3),
                        rs.getDate(4), rs.getString(5), rs.getBoolean(6));
            }
            rs.close();
            query.close();

        } catch (SQLException e) {
            JOptionPane.showMessageDialog(null,
                    mess.getMessage("gestor.request.err.db") + "\n" + e.getMessage(),
                    mess.getMessage("gestor.request"), JOptionPane.ERROR_MESSAGE);
        } catch (Exception eAl) {
            JOptionPane.showMessageDialog(null,
                    mess.getMessage("gestor.request.err.unknown") + "\n" + eAl.getMessage(),
                    mess.getMessage("gestor.request"), JOptionPane.ERROR_MESSAGE);
        }
        return request;
    }

    /**
     * Llista de totes lis incidencies de la DB
     * @return Lista en totes les incidencies
     */
    public List<Incidencia> getRequestsList() {

        List<Incidencia> requestList = new ArrayList<Incidencia>();
        try {
            String consulta = "SELECT * FROM tb_request";

            Statement query = getConnection().createStatement();
            ResultSet rs = query.executeQuery(consulta);
            while (rs.next()) {
                requestList.add(new Incidencia(rs.getInt(1), rs.getInt(2), rs.getInt(3), rs.getDate(4), rs.getString(5), rs.getBoolean(6)));
            }
        } catch (SQLException e) {
            JOptionPane.showMessageDialog(null,
                    mess.getMessage("gestor.request.err.db") + "\n" + e.getMessage(),
                    mess.getMessage("gestor.request.err"), JOptionPane.ERROR_MESSAGE);
        } catch (Exception eAl) {
            JOptionPane.showMessageDialog(null,
                    mess.getMessage("gestor.request.err.unknown") + "\n" + eAl.getMessage(),
                    mess.getMessage("gestor.request.err"), JOptionPane.ERROR_MESSAGE);
        }
        return requestList;
    }

    /**
     * Dona d'alta un incidencia nova a la DB
     * @return 
     */
    public Integer addRequest(Incidencia request) {
        Integer ret = 1;

        try {
            java.util.Date fecha = new java.util.Date();
            java.sql.Date SQLFecha = new java.sql.Date(fecha.getTime());

            String consulta = "INSERT INTO tb_request (user_id, area_id, creation_date, "
                    + "description, is_request_solved) "
                    + " VALUES (?,?,?,?,?)";

            PreparedStatement paramQuery = getConnection().prepareStatement(consulta, PreparedStatement.RETURN_GENERATED_KEYS);
            paramQuery.setInt(1, request.getIdUser());
            paramQuery.setInt(2, request.getIdArea());
            paramQuery.setDate(3, SQLFecha);
            paramQuery.setString(4, request.getDescription());
            if (request.isSolved()) {
                paramQuery.setString(5, "Y");
            } else {
                paramQuery.setString(5, "N");
            }
            paramQuery.execute();

            ResultSet rs = paramQuery.getGeneratedKeys();
            while (rs.next()) {
                ret = rs.getInt(1);
            }
        } catch (SQLException e) {
            ret = -1;
            if (e.getSQLState().equals(GestorDisc.SQL_STATE_UNIQUE_VIOLATION)) {
                JOptionPane.showMessageDialog(null,
                        mess.getMessage("gestor.request.err.new") + "\n" + e.getMessage(),
                        mess.getMessage("gestor.request.err"), JOptionPane.ERROR_MESSAGE);
            }
        }
        return ret;
    }

    public Integer updateRequest(Integer idRequest, Boolean solved) {
        Integer ret = -1;
        Incidencia Request = null;

        try {
            Request = this.getRequest(idRequest);
            if (Request.isSolved() != solved) {
                String consulta = "UPDATE tb_request SET is_request_solved = ? WHERE request_id = ?";
                PreparedStatement paramQuery = getConnection().prepareStatement(consulta, PreparedStatement.RETURN_GENERATED_KEYS);
                paramQuery.setInt(2, idRequest);
                if (solved) {
                    paramQuery.setString(1, "Y");
                } else {
                    paramQuery.setString(1, "N");
                }
                paramQuery.execute();
                ResultSet rs = paramQuery.getGeneratedKeys();
                while (rs.next()) {
                    ret = rs.getInt(1);
                }
            }
        } catch (SQLException e) {
            ret = -1;
            if (e.getSQLState().equals(GestorDisc.SQL_STATE_UNIQUE_VIOLATION)) {
                JOptionPane.showMessageDialog(null,
                        mess.getMessage("gestor.request.err.update") + "\n" + e.getMessage(),
                        mess.getMessage("gestor.request.err"), JOptionPane.ERROR_MESSAGE);
            }
        }
        return ret;
    }

    public List<Incidencia> getRequestsList(boolean solved) {

        String opt = "N";
        List<Incidencia> requestList = new ArrayList<Incidencia>();
        if (solved) {
            opt = "Y";
        }
        String consulta = "SELECT * FROM tb_request WHERE is_request_solved='" + opt + "'";
        try {
            Statement query = getConnection().createStatement();
            ResultSet rs = query.executeQuery(consulta);
            while (rs.next()) {
                requestList.add(new Incidencia(rs.getInt(1), rs.getInt(2), rs.getInt(3), rs.getDate(4), rs.getString(5), rs.getBoolean(6)));
            }

        } catch (SQLException e) {
            JOptionPane.showMessageDialog(null,
                    mess.getMessage("gestor.request.err.db") + "\n" + e.getMessage(),
                    mess.getMessage("gestor.request.err"), JOptionPane.ERROR_MESSAGE);
        } catch (Exception eAl) {
            JOptionPane.showMessageDialog(null,
                    mess.getMessage("gestor.request.err.unknown") + "\n" + eAl.getMessage(),
                    mess.getMessage("gestor.request.err"), JOptionPane.ERROR_MESSAGE);
        }
        return requestList;
    }
}
